# -*- coding: utf-8 -*-
import codecs
# import pymssql
import pyodbc
import traceback
# import ejff
# import config_json
# config_json.read_setting()
# conn = sqlite3.connect('data.sqlite')
SERVER = '10.0.108.107'
USERNAME = 'lims'
PASSWORD = 'QmPz123@me'
DATABASE = 'caiji'
def myZhengli(raw):
    print(dir(raw))
    fs=raw.description
    res=[]
    for one in raw:
        o1={}
        i=0
        for a in one:
            o1[fs[i][0]]=a
            i+=1
        res.append(o1)
    return res
def testRaw():
    cmd="select * from parts_contact  where work_month IS NULL   and tiaoshi_date between '2016-10-17' and '2016-12-17'"
    raw=c.execute(cmd)
    r=myZhengli(raw)    
    print(r)
def update1():
    c.execute('alter table parts_contact add column work_month date')    
    conn.commit()
def updateDb():
    SERVER = '10.0.108.107'
    USERNAME = 'lims'
    PASSWORD = 'qwe123@'
    DATABASE = 'bj_caiji'
    connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};ENCRYPT=yes;TrustServerCertificate=yes;UID={USERNAME};PWD={PASSWORD}'
    conn = pyodbc.connect(connectionString)
    cmds=codecs.open("caiji.sql","r",'utf-8').read().split(";")
    cursor = conn.cursor()
    for cmd  in cmds:
        print(cmd)
        cursor.execute(cmd)
        # except sqlite3.OperationalError as e:
        #     traceback.print_exc()
        #     if "already exists" in str(e):
        #         pass
        #     else:
        #         pass
        #         # input("pause")
        # except sqlite3.IntegrityError as e:
        #     pass
    conn.commit()
    cursor.close()
    conn.close()
def up_syy():
    data=config_json.setting["syy"]
    print(data)
    for one in data:
        try:
            print(one)
            cmd=f"insert into syy(name) values('{one}')"
            c.execute(cmd)
        except sqlite3.OperationalError as e:
            traceback.print_exc()
            if "already exists" in str(e):
                pass
            else:
                pass
                # input("pause")
        except sqlite3.IntegrityError as e:
            pass
    conn.commit()
def getSyy():
    connectionString = 'DRIVER=ODBC Driver 18 for SQL Server;SERVER=%s;DATABASE=%s;ENCRYPT=yes;TrustServerCertificate=yes;UID=%s;PWD=%s' %(SERVER,DATABASE,USERNAME,PASSWORD)
    # connectionString = f'DRIVER={{ODBC Driver 18 for SQL Server}};SERVER={SERVER};DATABASE={DATABASE};ENCRYPT=yes;TrustServerCertificate=yes;UID={USERNAME};PWD={PASSWORD}'
    conn = pyodbc.connect(connectionString)
    cursor = conn.cursor()
    cmd="select * from bj_syy"
    cursor.execute(cmd)
    while True:
        row = cursor.fetchone()
        print(row)
        if not row:
            break
    conn.commit()
    cursor.close()
    conn.close()
if __name__=="__main__":    
    # updateDb()    
    getSyy()
